﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLPM.DTO;

namespace QLPM.DAO
{
    public class BenhNhanDAO
    {
        DataProvider provider;

        public BenhNhanDAO()
        {
            provider = new DataProvider();
        }

        public List<BenhNhan> DanhSachBenhNhan()
        {
            String sql = "select MaBenhNhan, TenBenhNhan, GioiTinh, NgaySinh, DiaChi from BENHNHAN";
            provider = new DataProvider("BENHNHAN",sql);
            DataTable dt = provider;
            List<BenhNhan> ds = new List<BenhNhan>();
            string[] phancach = new string[] {" "};
            string[] arr = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                BenhNhan bn = new BenhNhan();
                bn.MaBenhNhan = Convert.ToInt32(dt.Rows[i]["MaBenhNhan"].ToString());
                bn.TenBenhNhan = dt.Rows[i]["TenBenhNhan"].ToString();
                if (dt.Rows[i]["GioiTinh"].ToString() == "1")
                {
                    bn.GioiTinh = "Nữ";
                }
                else
                {
                    bn.GioiTinh = "Nam";
                }
                string ngaysinh = dt.Rows[i]["NgaySinh"].ToString();
                arr = ngaysinh.Split(phancach, StringSplitOptions.None);
                ngaysinh = arr[0].ToString();
                bn.NgaySinh = Function.Convert_date(ngaysinh, "mm/dd/yyyy", "dd/mm/yyyy");
                bn.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                ds.Add(bn);
            }
            return ds;
        }

        public List<BenhNhan> TraCuuBenhNhan(string sql1)
        {
            String sql = "select * from BENHNHAN where 1 = 0 " + sql1;
            provider = new DataProvider("BENHNHAN", sql);
            DataTable dt = provider;
            List<BenhNhan> ds = new List<BenhNhan>();
            string[] phancach = new string[] { " " };
            string[] arr = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                BenhNhan bn = new BenhNhan();
                bn.MaBenhNhan = Convert.ToInt32(dt.Rows[i]["MaBenhNhan"].ToString());
                bn.TenBenhNhan = dt.Rows[i]["TenBenhNhan"].ToString();
                if (dt.Rows[i]["GioiTinh"].ToString() == "1")
                {
                    bn.GioiTinh = "Nữ";
                }
                else
                {
                    bn.GioiTinh = "Nam";
                }
                string ngaysinh = dt.Rows[i]["NgaySinh"].ToString();
                arr = ngaysinh.Split(phancach, StringSplitOptions.None);
                ngaysinh = arr[0].ToString();
                bn.NgaySinh = Function.Convert_date(ngaysinh, "mm/dd/yyyy", "dd/mm/yyyy");
                bn.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                ds.Add(bn);
            }
            return ds;
        }

        public int ThemBenhNhan(BenhNhan bn)
        {
            String sql = String.Format("insert into BENHNHAN(TenBenhNhan, GioiTinh, NgaySinh, DiaChi) values('{0}',{1},#{2}#,'{3}')", bn.TenBenhNhan, bn.GioiTinh, bn.NgaySinh, bn.DiaChi );
            return provider.Excute_Non_Query(sql);
        }

        public int XoaBenhNhan(int mabenhnhan)
        {
            String sql = String.Format("delete from BENHNHAN where MaBenhNhan = {0}", mabenhnhan);
            return provider.Excute_Non_Query(sql);
        }

        public int CapNhatBenhNhan(BenhNhan bn)
        {
            String sql = String.Format("update BENHNHAN set TenBenhNhan = '{0}', GioiTinh = {1}, NgaySinh = #{2}#, DiaChi = '{3}' where MaBenhNhan = {4}", bn.TenBenhNhan, bn.GioiTinh, bn.NgaySinh, bn.DiaChi, bn.MaBenhNhan);
            return provider.Excute_Non_Query(sql);
        }

        public int SoBNDaKham(String ngay)
        {
            String sql = String.Format("select count(*) as TongBN from KHAMBENH where NgayKham = '{0}'", ngay);
            provider = new DataProvider("KHAMBENH", sql);
            DataTable dt = provider;
            int sl = Convert.ToInt32(dt.Rows[0]["TongBN"].ToString());
            return sl;
        }

        public List<int> LayDSBNDangKham(string ngaykham)
        {
            String sql = String.Format("select BENHNHAN.MaBenhNhan, KHAMBENH.MaLoaiBenh from BENHNHAN, KHAMBENH where BENHNHAN.MaBenhNhan = KHAMBENH.MaBenhNhan and KHAMBENH = '{0}'", ngaykham);
            provider = new DataProvider("BENHNHAN, KHAMBENH", sql);
            DataTable dt = provider;
            List<int> lt = new List<int>();
            for( int i = 0; i < dt.Rows.Count; i++)
            {
                int tam = Convert.ToInt32(dt.Rows[i]["MaBenhNhan"].ToString());
                lt.Add(tam);
            }
            return lt;
        }

        public int CapNhatChuaLapPhieu(int mabn)
        {
            String sql = String.Format("update BENHNHAN set DaLapPhieu = 0 where MaBenhNhan = {0}", mabn);
            return provider.Excute_Non_Query(sql);
        }

        public int CapNhatChuaThanhToan(int mabn)
        {
            String sql = String.Format("update BENHNHAN set DaThanhToan = 0 where MaBenhNhan = {0}", mabn);
            return provider.Excute_Non_Query(sql);
        }

        public int CapNhatDaLapPhieu(int mabn)
        {
            String sql = String.Format("update BENHNHAN set DaLapPhieu = 1 where MaBenhNhan = {0}", mabn);
            return provider.Excute_Non_Query(sql);
        }

        public int CapNhatDaThanhToan(int mabn)
        {
            String sql = String.Format("update BENHNHAN set DaThanhToan = 1 where MaBenhNhan = {0}", mabn);
            return provider.Excute_Non_Query(sql);
        }

        public List<BenhNhan> DanhSachKhamBenh(string ngaykham)
        {
            String sql = String.Format("select BENHNHAN.MaBenhNhan, TenBenhNhan, GioiTinh, NgaySinh, DiaChi, MaLoaiBenh, BENHNHAN.DaLapPhieu, BENHNHAN.DaThanhToan from BENHNHAN, KHAMBENH where BENHNHAN.MaBenhNhan = KHAMBENH.MaBenhNhan and KHAMBENH.NgayKham='{0}' and BENHNHAN.DaThanhToan = 0", ngaykham);
            provider = new DataProvider("BENHNHAN, KHAMBENH",sql);
            DataTable dt = provider;
            List<BenhNhan> ds = new List<BenhNhan>();
            string[] phancach = new string[] { " " };
            string[] arr = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                BenhNhan bn = new BenhNhan();
                bn.MaBenhNhan = Convert.ToInt32(dt.Rows[i]["MaBenhNhan"].ToString());
                bn.TenBenhNhan = dt.Rows[i]["TenBenhNhan"].ToString();
                if (dt.Rows[i]["GioiTinh"].ToString() == "1")
                {
                    bn.GioiTinh = "Nữ";
                }
                else
                {
                    bn.GioiTinh = "Nam";
                }
                string ngaysinh = dt.Rows[i]["NgaySinh"].ToString();
                arr = ngaysinh.Split(phancach, StringSplitOptions.None);
                ngaysinh = arr[0].ToString();
                bn.NgaySinh = Function.Convert_date(ngaysinh, "mm/dd/yyyy", "dd/mm/yyyy");
                bn.DiaChi = dt.Rows[i]["DiaChi"].ToString();
                if (dt.Rows[i]["DaThanhToan"].ToString() == "0")
                {
                    bn.DaThanhToan = " ";
                }
                if (dt.Rows[i]["DaThanhToan"].ToString() == "1")
                {
                    bn.DaThanhToan = "Đã Thanh Toán";
                }
                if (dt.Rows[i]["DaLapPhieu"].ToString() == "0")
                {
                    bn.DaLapPhieu = " ";
                }
                if (dt.Rows[i]["DaLapPhieu"].ToString() == "1")
                {
                    bn.DaLapPhieu = "Đã Lập Phiếu";
                }
                ds.Add(bn);
            }
            return ds;
        }

        public int LayTinhTrangLP(int maBN)
        {
            String sql = String.Format("select DaLapPhieu from BENHNHAN where MaBenhNhan={0}", maBN);
            provider = new DataProvider("BENHNHAN", sql);
            DataTable dt = provider;
            int so = Convert.ToInt32(dt.Rows[0]["DaLapPhieu"].ToString());
            return so;
        }

        public int LayTinhTrangTT(int maBN)
        {
            String sql = String.Format("select DaThanhToan from BENHNHAN where MaBenhNhan={0}", maBN);
            provider = new DataProvider("BENHNHAN", sql);
            DataTable dt = provider;
            int so = Convert.ToInt32(dt.Rows[0]["DaThanhToan"].ToString());
            return so;
        }
    }
}
